﻿
// <copyright file="DatabaseFunction.cs" company="Fsoft">
//  coppy right of ORT 
// </copyright>
// <summary>
//   The database function.
// </summary>
// --------------------------------------------------------------------------------------------------------------------
namespace CMS.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Transactions;

    /// <summary>
    /// The database function.
    /// </summary>
    public class DatabaseFunction
    {
        /// <summary>
        /// The create sql parameter.
        /// </summary>
        /// <param name="name">
        /// The name.
        /// </param>
        /// <param name="value">
        /// The value.
        /// </param>
        /// <returns>
        /// The System.Data.SqlClient.SqlParameter.
        /// </returns>
        public static SqlParameter CreateSqlParameter(string name, object value)
        {
            return new SqlParameter(name.Contains("@") ? name : "@" + name, value == null ? DBNull.Value : value);
        }

        /// <summary>
        /// The create sql parameter.
        /// </summary>
        /// <param name="name">
        /// The name.
        /// </param>
        /// <param name="sqlDbType">
        /// The sql db type.
        /// </param>
        /// <param name="value">
        /// The value.
        /// </param>
        /// <returns>
        /// The System.Data.SqlClient.SqlParameter.
        /// </returns>
        public static SqlParameter CreateSqlParameter(string name, SqlDbType sqlDbType, object value)
        {
            return CreateSqlParameter(name, sqlDbType, value, false);
        }

        /// <summary>
        /// The create sql parameter.
        /// </summary>
        /// <param name="name">
        /// The name.
        /// </param>
        /// <param name="sqlDbType">
        /// The sql db type.
        /// </param>
        /// <param name="value">
        /// The value.
        /// </param>
        /// <param name="isOutPut">
        /// The is out put.
        /// </param>
        /// <returns>
        /// The System.Data.SqlClient.SqlParameter.
        /// </returns>
        public static SqlParameter CreateSqlParameter(string name, SqlDbType sqlDbType, object value, bool isOutPut)
        {
            SqlParameter returnValue = new SqlParameter(name.Contains("@") ? name : "@" + name, sqlDbType);
            returnValue.Value = value == null ? DBNull.Value : value;
            if (isOutPut)
            {
                returnValue.Direction = ParameterDirection.Output;
            }

            return returnValue;
        }

        /// <summary>
        /// The create sql parameter.
        /// </summary>
        /// <param name="namesAndValue">
        /// The names and value.
        /// </param>
        /// <returns>
        /// The System.Collections.Generic.List`1[T -&gt; System.Data.SqlClient.SqlParameter].
        /// </returns>
        public static List<SqlParameter> CreateSqlParameter(params object[] namesAndValue)
        {
            List<SqlParameter> returnValue = new List<SqlParameter>();

            for (int i = 0; i < namesAndValue.Length; i = i + 2)
            {
                returnValue.Add(CreateSqlParameter(namesAndValue[i].ToString(), namesAndValue[i + 1]));
            }

            return returnValue;
        }

        /// <summary>
        /// The execute non query.
        /// </summary>
        /// <param name="storeProcedureName">
        /// The store procedure name.
        /// </param>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <returns>
        /// The System.Int32.
        /// </returns>
        public static int ExecuteNonQuery(string storeProcedureName, string connectionString = null, int? timeOut = null)
        {
            return ExecuteNonQuery(storeProcedureName, new List<SqlParameter>(), connectionString, timeOut);
        }

        /// <summary>
        /// The execute non query.
        /// </summary>
        /// <param name="storeProcedureName">
        /// The store procedure name.
        /// </param>
        /// <param name="sqlParameters">
        /// The sql parameters.
        /// </param>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <returns>
        /// The System.Int32.
        /// </returns>
        public static int ExecuteNonQuery(string storeProcedureName, SqlParameter sqlParameters, string connectionString = null, int? timeOut = null)
        {
            return ExecuteNonQuery(storeProcedureName, new List<SqlParameter> { sqlParameters }, connectionString, timeOut);
        }

        /// <summary>
        /// The execute non query.
        /// </summary>
        /// <param name="storeProcedureName">
        /// The store procedure name.
        /// </param>
        /// <param name="sqlParameters">
        /// The sql parameters.
        /// </param>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <returns>
        /// The System.Int32.
        /// </returns>
        public static int ExecuteNonQuery(string storeProcedureName, List<SqlParameter> sqlParameters, string connectionString = null, int? timeOut = null)
        {
            return CreateSqlCommand(storeProcedureName, sqlParameters, cmd => cmd.ExecuteNonQuery(), connectionString, timeOut);
        }

        /// <summary>
        /// The get data.
        /// </summary>
        /// <param name="storeProcedureName">
        /// The store procedure name.
        /// </param>
        /// <param name="readerData">
        /// The reader data.
        /// </param>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <typeparam name="T"> any type object return
        /// </typeparam>
        /// <returns>
        /// The T.
        /// </returns>
        public static T GetData<T>(string storeProcedureName, Func<SqlDataReader, T> readerData, string connectionString = null, int? timeOut = null)
        {
            return GetData<T>(storeProcedureName, new List<SqlParameter>(), readerData, connectionString, timeOut);
        }

        /// <summary>
        /// The get data.
        /// </summary>
        /// <param name="storeProcedureName">
        /// The store procedure name.
        /// </param>
        /// <param name="sqlParameters">
        /// The sql parameters.
        /// </param>
        /// <param name="readerData">
        /// The reader data.
        /// </param>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <typeparam name="T"> any type object return
        /// </typeparam>
        /// <returns>
        /// The T.
        /// </returns>
        public static T GetData<T>(string storeProcedureName, SqlParameter sqlParameters, Func<SqlDataReader, T> readerData, string connectionString = null, int? timeOut = null)
        {
            return GetData<T>(storeProcedureName, new List<SqlParameter> { sqlParameters }, readerData, connectionString, timeOut);
        }

        /// <summary>
        /// The get data.
        /// </summary>
        /// <param name="storeProcedureName">
        /// The store procedure name.
        /// </param>
        /// <param name="sqlParameters">
        /// The sql parameters.
        /// </param>
        /// <param name="readerData">
        /// The reader data.
        /// </param>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <typeparam name="T"> any type object return
        /// </typeparam>
        /// <returns>
        /// The T.
        /// </returns>
        public static T GetData<T>(string storeProcedureName, List<SqlParameter> sqlParameters, Func<SqlDataReader, T> readerData, string connectionString = null, int? timeOut = null)
        {
            T returnValue = default(T);

            CreateSqlCommand(
                storeProcedureName,
                sqlParameters,
                cmd =>
                {
                    var reader = cmd.ExecuteReader();

                    returnValue = readerData(reader);
                }, connectionString, timeOut);

            return returnValue;
        }

        /// <summary>
        /// The get data.
        /// </summary>
        /// <param name="storeProcedureName">
        /// The store procedure name.
        /// </param>
        /// <param name="sqlParameters">
        /// The sql parameters.
        /// </param>
        /// <param name="readerData">
        /// The reader data.
        /// </param>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <typeparam name="T"> any type object return
        /// </typeparam>
        /// <returns>
        /// The T.
        /// </returns>
        public static T GetData<T>(string storeProcedureName, List<SqlParameter> sqlParameters, Action<SqlCommand> customizeSqlCommand, Func<SqlDataReader, T> readerData, string connectionString = null, int? timeOut = null)
        {
            T returnValue = default(T);

            CreateSqlCommand(
                storeProcedureName,
                sqlParameters,
                cmd =>
                {
                    customizeSqlCommand(cmd);
                    var reader = cmd.ExecuteReader();

                    returnValue = readerData(reader);
                }, connectionString, timeOut);

            return returnValue;
        }


        /// <summary>
        /// The bulk insert.
        /// </summary>
        /// <param name="dataTable">
        /// The data table.
        /// </param>
        public static void BulkInsert(DataTable dataTable, bool isForceTrigger = false)
        {
            using (
                    SqlBulkCopy bulk = new SqlBulkCopy(ConfigureKeys.ConnectionStringStandard,
                                                       isForceTrigger ? SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.KeepIdentity)
                    )
            {
                dataTable.Columns.OfType<DataColumn>().ToList()
                    .ForEach(c => bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName)));
                bulk.DestinationTableName = dataTable.TableName;
                bulk.WriteToServer(dataTable);
            }

        }

        /// <summary>
        /// The transaction.
        /// </summary>
        /// <param name="insertUpdateDeleteOthers">
        /// The insert update delete others.
        /// </param>
        protected void Transaction(Action insertUpdateDeleteOthers)
        {
            using (TransactionScope transactionScope = new TransactionScope())
            {
                try
                {
                    insertUpdateDeleteOthers();
                    transactionScope.Complete();
                }
                finally
                {
                    transactionScope.Dispose();
                }
            }
        }

        #region Private method

        /// <summary>
        /// The create sql command.
        /// </summary>
        /// <param name="storeProcedureName">
        /// The store procedure name.
        /// </param>
        /// <param name="sqlParameters">
        /// The sql parameters.
        /// </param>
        /// <param name="actionSqlCommand">
        /// The action sql command.
        /// </param>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <returns>
        /// The System.Int32.
        /// </returns>
        private static int CreateSqlCommand(string storeProcedureName, List<SqlParameter> sqlParameters, Action<SqlCommand> actionSqlCommand, string connectionString = null, int? timeOut = null)
        {
            int result = 0;
            if (string.IsNullOrWhiteSpace(connectionString))
            {
                connectionString = ConfigureKeys.ConnectionStringStandard;
            }

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = storeProcedureName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 300;//5 minute
                    if (timeOut.HasValue) cmd.CommandTimeout = timeOut.Value;

                    if (sqlParameters != null && sqlParameters.Count > 0)
                    {
                        cmd.Parameters.AddRange(sqlParameters.ToArray());
                    }

                    actionSqlCommand(cmd);

                    if (sqlParameters != null && sqlParameters.Count > 0)
                    {
                        var parameterName = (from row in sqlParameters
                                             where row.Direction == ParameterDirection.Output
                                             select row.ParameterName).FirstOrDefault();
                        if (parameterName != null)
                        {
                            result = Convert.ToInt32(cmd.Parameters[parameterName].Value);
                        }
                    }
                }
            }

            return result;
        }
        #endregion

    }
}
